package com.vnp.cooking.database;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.LinkedHashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class DBAdapter {
	private static final String TAG = DBAdapter.class.getSimpleName();
	public static final int FIELD_TYPE_INTEGER = 0x01;
	public static final int FIELD_TYPE_FLOAT = 0x02;
	public static final int FIELD_TYPE_STRING = 0x03;
	public static final int FIELD_TYPE_BLOB = 0x04;
	public static final int FIELD_TYPE_NULL = 0x00;
	private Context context;
	private MySQLiteHelper dbHelper;
	private SQLiteDatabase db;
	private volatile static DBAdapter instance;
	
	public DBAdapter(Context context) {
		this.context = context;
	}
	
	public static DBAdapter getInstance(Context context) {
		if (instance == null) {
			synchronized (DBAdapter.class) {
				if (instance == null) {
					instance = new DBAdapter(context);
				}
			}
		}
		return instance;
	}
	
	public void open() {
		if (!isOpen()) {
			dbHelper = new MySQLiteHelper(context);
			db = dbHelper.getWritableDatabase();
			Log.d(TAG, "call open database, database isOpen: "+db.isOpen());
		}
	}
	
	public boolean isOpen() {
		if (dbHelper != null && db != null) {
			if (db.isOpen()) return true;
		}
		return false;
	}
	
	public void close() {
		if (db != null) {
			if (db.isOpen()) db.close(); db = null;
		}
		if (dbHelper != null) {
			dbHelper.close();
			dbHelper = null;
		}
	}
	
	public ArrayList<LinkedHashMap<String, Object>> fetchAll(String tableName, String whereClause, String orderby) {
		return fetchAll(tableName, whereClause, orderby, 0, 0);
	}
	
	public ArrayList<LinkedHashMap<String, Object>> fetchAll(String tableName, String whereClause, String orderby, int limit, int offset) {
		return fetch(tableName, QueryBuilder.ALL_COLUMN, whereClause, orderby, limit, offset, null);
	}
	
	public boolean contains(String tableName, String column, String whereClause) {
		LinkedHashMap<String, Object> data = fetchOne(tableName, new String[] {column}, whereClause, null);
		if (data != null) { 
			if (data.size()>0) return true;
		}
		return false;
	}
	
	public LinkedHashMap<String, Object> fetchOne(String tableName, String[] columns, String whereClause, String orderby) {
		ArrayList<LinkedHashMap<String, Object>> data = fetch(tableName, columns, whereClause, orderby, 1, 0, null);
		if (data.size()>0) return data.get(0);
		return new LinkedHashMap<String, Object>();
	}
	
	public ArrayList<LinkedHashMap<String, Object>> fetch(String tableName, String[] columns, String whereClause, String orderby, int limit, int offset, String otherwhereClause) {
		ArrayList<LinkedHashMap<String, Object>> result = new ArrayList<LinkedHashMap<String, Object>>();
		if (isOpen()) {
			Hashtable<String, Integer> columnsOfTable = getAllOfColumnsOfTable(tableName);
			String sqlQuery = QueryBuilder.selectCommandSql(tableName, columns, whereClause, orderby, limit, offset, otherwhereClause);
			System.out.println("SQL Query = "+sqlQuery);
			Cursor c = db.rawQuery(sqlQuery, null);
			if (c != null) {
				if (c.moveToFirst()) {
					do {
						LinkedHashMap<String, Object> data = new LinkedHashMap<String, Object>();
						if (columns.length == 1 && columns[0].equals("*")) { columns = c.getColumnNames(); }
						for(String column : columns) {
							int idx = c.getColumnIndex(column);
							int typeOfColumn = columnsOfTable.get(column);
							switch (typeOfColumn) {
								case FIELD_TYPE_STRING:
									data.put(column, new String(c.getString(idx)));
									break;
								case FIELD_TYPE_FLOAT:
									data.put(column, new Float(c.getFloat(idx)));
									break;
								case FIELD_TYPE_INTEGER:
									data.put(column, new Integer(c.getInt(idx)));
									break;
								case FIELD_TYPE_BLOB:
									data.put(column, new String(c.getBlob(idx)));
									break;
							}
						}
						if (data.size()>0) result.add(data);
					} while(c.moveToNext());
				}
			}
			c.close(); c = null;
		}
		return result;
	}
	
	public Hashtable<String, Integer> getAllOfColumnsOfTable(String tableName) {
		Hashtable<String, Integer> data = new Hashtable<String, Integer>();
		try {
			Cursor c = db.rawQuery("PRAGMA table_info("+tableName+")", null);
			if (c != null) {
				if (c.moveToFirst()) {
					do {
						String nameOfColumn = c.getString(c.getColumnIndex("name"));
						String typeOfColumn = c.getString(c.getColumnIndex("type"));
						int type = FIELD_TYPE_NULL;
						if (typeOfColumn.matches(".*INT.*")) {
							type = FIELD_TYPE_INTEGER;
						} else if (typeOfColumn.matches(".*CHAR.*|TEXT|CLOB")) {
							type = FIELD_TYPE_STRING;
						} else if (typeOfColumn.matches(".*DOUBLE.*|REAL|FLOAT")) {
							type = FIELD_TYPE_FLOAT;
						} else if (typeOfColumn.matches("BLOB")) {
							type = FIELD_TYPE_BLOB;
						} else {
							type = FIELD_TYPE_NULL;
						}
						data.put(nameOfColumn, type);
					} while(c.moveToNext());
				}
				c.close(); c = null;
			}
		} catch(SQLiteException e) {
			e.printStackTrace();
		}
		return data;
	}
	
	public void insert(String tableName, ContentValues values) {
		if (values == null) return;
		if (isOpen()) {
			try {
				db.insert(tableName, null, values);
			} catch(SQLiteException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void insert(String tableName, LinkedHashMap<String, Object> values) {
		if (values == null) return;
		if (isOpen()) {
			try {
				StringBuilder sql = new StringBuilder("INSERT INTO "+tableName+"(");
				for(String key : values.keySet()) {
					sql.append(key+",");
				}
				sql.deleteCharAt(sql.length()-1);
				sql.append(") VALUES(");
				for(Object v : values.values()) {
					if (v instanceof String) {
						sql.append(String.format("'%s',", String.valueOf(v)));
					} else {
						sql.append(String.valueOf(v)+",");
					}
				}
				sql.deleteCharAt(sql.length()-1);
				sql.append(");");
				db.execSQL(sql.toString());
			} catch(SQLiteException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public boolean update(String tableName, ContentValues values, String whereClause) {
		boolean isSuccess = false;
		if (isOpen()) {
			try {
				if (db.update(tableName, values, whereClause, null)>0) isSuccess = true;
			} catch(SQLiteException e) {
				e.printStackTrace();
			}
		}
		return isSuccess;
	}
	
	public void update(String tableName, String setValues, String whereClause) {
		try {
			StringBuilder sb = new StringBuilder("UPDATE "+tableName+" SET "+setValues);
			if (whereClause!=null && !whereClause.equals("")) {
				sb.append(" WHERE "+whereClause);
			}
			db.execSQL(sb.toString());
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void excuteSQL(String sql) {
		try {
			db.execSQL(sql);
		} catch(SQLiteException e) {
			e.printStackTrace();
		}
	}
	
	public boolean delete(String tableName, String whereClause) {
		boolean isSuccess = false;
		if (isOpen()) {
			try {
				if (db.delete(tableName, whereClause, null)>0) isSuccess = true;
			} catch(SQLiteException e) {
				e.printStackTrace();
			}
		}
		return isSuccess;
	}
	
	private static class QueryBuilder {
		
		public static final String[] ALL_COLUMN = new String[] { "*" };
		
		public static String selectCommandSql(String tableName, String[] columns, String whereClause, String orderby, int limit, int offset, String owhereClause) {
			StringBuilder sb = new StringBuilder("SELECT ");
			for(int i=0;i<columns.length-1;i++) {
				sb.append(columns[i]+",");
			}
			if (columns.length>0) sb.append(columns[columns.length - 1]+" ");
			sb.append("FROM "+tableName+" ");
			if (whereClause!=null && !("".equals(whereClause))) sb.append("WHERE "+whereClause+" ");
			if (orderby!=null && !(orderby.equals(""))) sb.append("ORDER BY "+orderby+" ");
			if (limit>0) sb.append("LIMIT "+limit+" ");
			if (offset>0) sb.append("OFFSET "+offset+" ");
			if (owhereClause!=null && !(whereClause.equals(""))) sb.append(whereClause);
			return sb.toString();
		}
	}
	
	@Override
	protected void finalize() throws Throwable {
		close(); super.finalize();
	}
	
}
